using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ELearning.Models;

namespace ELearning.DAL
{
    /// <summary>
    /// EMenu数据访问层
    /// </summary>
	public partial class EMenuRepository : BaseRepository<EMenu>
    {
        /// <summary>
        /// 根据登录账号查找所有权限
        /// </summary>
        /// <param name="loginName">登录名称</param>
        /// <returns></returns>
        public List<EMenu> GetUserMenusByLoginInfo(string loginName)
        {
            var sql = @"with cte as
                         (
                         SELECT RoleID,ParentID,RoleName,Code
                             FROM ERole
                             WHERE RoleID in(select distinct b.RoleId from Esysuser a inner join RRoleUser b on a.userId=b.userid where a.IsDelete=0 and a.loginname=@loginName)
                             UNION ALL
                         select d.RoleID,d.ParentID,d.RoleName,d.Code  from cte c inner join ERole d
                         on c.ParentID = d.RoleID
                         )
                         --递归角色关系
                         select m.* from cte inner join RMenuUR e
                         on cte.RoleID=e.KeyId inner join EMenu m
                         on e.MenuId=m.ID where m.IsDelete=0
                         union 
                         --直接个人关系
                         select m.* from Esysuser u inner join RMenuUR e
                         on u.UserID=e.KeyId inner join EMenu m
                         on e.MenuId=m.ID where m.IsDelete=0 and u.IsDelete=0 and u.LoginName=@loginName";
            var args = new SqlParameter[] {
                    new SqlParameter("@loginName",loginName)
                 };
            var modelList = nContext.Database.SqlQuery<EMenu>(sql, args).OrderBy(m => m.Sort).ToList();
            return modelList;
        }
        /// <summary>
        /// 根据用户ID
        /// </summary>       
        /// <returns></returns>
        public List<EMenu> GetUserMenusByLoginInfo(Guid userId)
        {
            var sql = @"with cte as
                         (
                         SELECT RoleID,ParentID,RoleName,Code
                             FROM ERole
                             WHERE RoleID in(select distinct b.RoleId from Esysuser a inner join RRoleUser b on a.userId=b.userid where a.IsDelete=0 and a.UserID=@UserID)
                             UNION ALL
                         select d.RoleID,d.ParentID,d.RoleName,d.Code  from cte c inner join ERole d
                         on c.ParentID = d.RoleID
                         )
                         --递归角色关系
                         select distinct m.* from cte inner join RMenuUR e
                         on cte.RoleID=e.KeyId inner join EMenu m
                         on e.MenuId=m.ID where m.IsDelete=0";
              var args = new SqlParameter[] {
                    new SqlParameter("@UserID",userId)
                 };
            var modelList = nContext.Database.SqlQuery<EMenu>(sql, args).OrderBy(m => m.Sort).ToList();
            return modelList;
        }


        #region 路由相关-用户权限及菜单
        /// <summary>
        /// 根据登录账号查找所有权限
        /// </summary>
        /// <param name="UserID">用户Id</param>
        /// <returns></returns>
        public List<string> GetUserPermission(Guid userID)
        {
            var sql = @"  
                          select distinct d.Code from RRoleUser a,ERole b,RMenuUR c,EMenu d
                          where a.UserID=@UserID
                          and b.IsDelete=0 and a.RoleID=b.RoleID
                          and c.IsDelete=0 and c.KeyId=b.RoleID
                          and d.IsDelete=0 and d.Id=c.MenuId;";
            var args = new SqlParameter[] {
                    new SqlParameter("@UserID",userID)
                 };
            var modelList = nContext.Database.SqlQuery<string>(sql, args).ToList();
            return modelList;
        }
        #endregion

        #region 无用代码
        /// <summary>
        /// 通过用户ID查找菜单权限
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public List<EMenu> GetUserMenusByUserId(string userId)
        {
            var sql = @"
                --直接个人关系
 
                SELECT  m.[Id],[ParentId],[Title],[Type],[Code],[Icon],[Path],[Redirect],[Name],[Component],[NoCache],[Affix],[Sort],
                m.IsDelete,m.Creater,m.CreateTime,m.IsUsed,m.Modifyer,m.ModifyTime FROM  [EMenu] m
                inner join 
                RMenuUR r on r.MenuId=m.Id 
                where m.IsDelete=0 and m.IsUsed=1 and  r.KeyId=(select UserID from ESysUser where LoginName=@userId)
                union 
                --直接角色关系
                SELECT  m.[Id],[ParentId],[Title],[Type],[Code],[Icon],[Path],[Redirect],[Name],[Component],[NoCache],[Affix],[Sort],
                m.IsDelete,m.Creater,m.CreateTime,m.IsUsed,m.Modifyer,m.ModifyTime FROM  [EMenu] m
                inner join 
                RMenuUR r on r.MenuId=m.Id 
                inner join RRoleUser c on c.RoleID=r.KeyId where  m.IsDelete=0 and m.IsUsed=1 and c.LoginName=@userId
               
                ";
            var args = new SqlParameter[] {
                    new SqlParameter("@userId",userId)
                 };
            var modelList = nContext.Database.SqlQuery<EMenu>(sql, args).OrderBy(m => m.Sort).ToList();
            return modelList;
        }

        /// <summary>
        /// 获取该菜单的所有父节点（包含本菜单）
        /// </summary>
        /// <param name="menuId">菜单id</param>
        /// <returns></returns>
	    public List<EMenu> GetParentMenusById(Guid menuId)
        {
            var sql = @"
    WITH    cte
        AS ( SELECT   *
                FROM     dbo.EMenu
                WHERE    ID = @menuId
                UNION ALL
                SELECT   d.*
                FROM     cte c
                        INNER JOIN dbo.EMenu d ON d.id = c.ParentId AND d.IsDelete = 0
        )
    SELECT  *
    FROM    cte ";

            var args = new[] {new SqlParameter("@menuId",menuId)
            };
            var modelList = nContext.Database.SqlQuery<EMenu>(sql, args).OrderBy(m => m.Sort).ToList();
            return modelList;
        }

        /// <summary>
        /// 获取该菜单的所有子节点（包含本菜单）
        /// </summary>
        /// <param name="menuId">菜单id</param>
        public List<EMenu> GetSubMenusById(Guid menuId)
        {
            var sql = @"
    WITH    cte
        AS ( SELECT   *
                FROM     dbo.EMenu
                WHERE    ID = @menuId
                UNION ALL
                SELECT   d.*
                FROM     cte c
                        INNER JOIN dbo.EMenu d ON d.ParentId = c.Id AND d.IsDelete = 0
        )
    SELECT  *
    FROM    cte ";

            var args = new[] {new SqlParameter("@menuId",menuId)
            };
            var modelList = nContext.Database.SqlQuery<EMenu>(sql, args).OrderBy(m => m.Sort).ToList();
            return modelList;
        }
        #endregion
    }
}


